package dbblink;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import dataTables.CDataTableIngredients;
import dataTables.CDataTableMaxCode;
import dataTables.CDataTableRecipes;
import dataTables.CDataTableRestaurants;
import dataTables.CDataTableSale;
import dataTables.CDataTableSupplier;
import dataTables.CDataTableSearchRecipe;
import dataTables.CDataTableSearchSales;
import dataTables.CDataTablesSearchIngredients;
import dataTables.CDataTablePassword;



public class CQuery {
	CLinkJDBC conexionJDBC;
	Connection conexion1;
	
	
	public void insertData(String ingreso) {

		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet resultado = null;
		String ingresa = ingreso;

		

		try {

			PreparedStatement estamentoPreparado = (PreparedStatement) conexion1
					.prepareStatement(ingreso);
			estamentoPreparado.execute();

		} catch (com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException ex) {
			if (ex
					.toString()
					.contains(
							"com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`billar/empleados`, CONSTRAINT `empleados_ibfk_1` FOREIGN KEY (`ciudad`) REFERENCES `ciudad` (`codigo`) ON DELETE CASCADE ON UPDATE CASCADE)"))
				JOptionPane.showMessageDialog(null,
						"El dato seleccionado no esta en la base de datos ",
						"ERROR", JOptionPane.ERROR_MESSAGE);
			if (ex
					.toString()
					.contains(
							"com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry "))
				JOptionPane
						.showMessageDialog(
								null,
								"El codigo ingresado ya se\n encuentra en la base de datos ",
								"EROOR", JOptionPane.ERROR_MESSAGE);

		} catch (SQLException ex) {
			JOptionPane.showMessageDialog(null,
					"No se pudo ingresar a la base de datos", "ERROR",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

	}
	
	
	public ArrayList<CDataTableRestaurants> showRestaurants(String sQuery) {

		ArrayList<CDataTableRestaurants> aRestaurantsList;
		aRestaurantsList = new ArrayList<CDataTableRestaurants>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet queryAnswer = null;
		String consulta = sQuery;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			queryAnswer = declaracionConsulta.executeQuery(sQuery);

			while (queryAnswer.next()) 
			{
				aRestaurantsList.add(new CDataTableRestaurants(queryAnswer.getInt(1),
								queryAnswer.getString(2),queryAnswer.getString(3),queryAnswer.getString(4),queryAnswer.getString(5)));
			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return aRestaurantsList;
	}
	

	public ArrayList<CDataTableIngredients> showIngredients(String sQuery) {

		ArrayList<CDataTableIngredients> aIngredientList;
		aIngredientList = new ArrayList<CDataTableIngredients>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet queryAnswer = null;
		String consulta = sQuery;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			queryAnswer = declaracionConsulta.executeQuery(sQuery);

			while (queryAnswer.next()) 
			{
				aIngredientList.add(new CDataTableIngredients(queryAnswer.getInt(1),
								queryAnswer.getString(2)));
			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return aIngredientList;
	}
	
	
	public ArrayList<CDataTableSupplier> showSuppliers(String sQuery) {

		ArrayList<CDataTableSupplier> aSupplierList;
		aSupplierList = new ArrayList<CDataTableSupplier>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet queryAnswer = null;
		String consulta = sQuery;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			queryAnswer = declaracionConsulta.executeQuery(sQuery);

			while (queryAnswer.next()) 
			{
				aSupplierList.add(new CDataTableSupplier(queryAnswer.getInt(1),
						queryAnswer.getString(2),queryAnswer.getString(3)));
			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return aSupplierList;
	}
	
	public ArrayList<CDataTableSearchRecipe> showRecipeList(String sQuery) {

		ArrayList<CDataTableSearchRecipe> aRecipeList;
		aRecipeList = new ArrayList<CDataTableSearchRecipe>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet queryAnswer = null;
		String consulta = sQuery;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			queryAnswer = declaracionConsulta.executeQuery(sQuery);

			while (queryAnswer.next()) 
			{
				aRecipeList.add(new CDataTableSearchRecipe(queryAnswer.getString(1),
						queryAnswer.getString(2),queryAnswer.getString(3),queryAnswer.getInt(4)));
			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return aRecipeList;
	}
	
	
	
	public ArrayList<CDataTablesSearchIngredients> showIngredientList(String sQuery) {

		ArrayList<CDataTablesSearchIngredients> aRecipeList;
		aRecipeList = new ArrayList<CDataTablesSearchIngredients>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet queryAnswer = null;
		String consulta = sQuery;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			queryAnswer = declaracionConsulta.executeQuery(sQuery);

			while (queryAnswer.next()) 
			{
				aRecipeList.add(new CDataTablesSearchIngredients(queryAnswer.getString(1),
						queryAnswer.getString(2)));
			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return aRecipeList;
	}
	
	
	public ArrayList<CDataTableSearchSales> showSaleList(String sQuery) {

		ArrayList<CDataTableSearchSales> aRecipeList;
		aRecipeList = new ArrayList<CDataTableSearchSales>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet queryAnswer = null;
		String consulta = sQuery;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			queryAnswer = declaracionConsulta.executeQuery(sQuery);

			while (queryAnswer.next()) 
			{
				aRecipeList.add(new CDataTableSearchSales(queryAnswer.getString(1),
						queryAnswer.getInt(2),queryAnswer.getString(3)));
			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return aRecipeList;
	}
	
	public ArrayList<CDataTableRecipes> showRecipes(String sQuery) {

		ArrayList<CDataTableRecipes> aRecipesList;
		aRecipesList = new ArrayList<CDataTableRecipes>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet queryAnswer = null;
		String consulta = sQuery;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			queryAnswer = declaracionConsulta.executeQuery(sQuery);

			while (queryAnswer.next()) 
			{
				aRecipesList.add(new CDataTableRecipes(queryAnswer.getInt(1),
								queryAnswer.getString(2),queryAnswer.getString(3),queryAnswer.getInt(4)));
			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return aRecipesList;
	}
	


	public ArrayList<CDataTablePassword> password(String consul) {

		ArrayList<CDataTablePassword> passwordList;
		passwordList = new ArrayList<CDataTablePassword>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet resultado = null;
		String consulta = consul;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			resultado = declaracionConsulta.executeQuery(consul);

			

			while (resultado.next()) {
				

				passwordList.add(new CDataTablePassword(resultado
						.getInt(1), resultado.getString(2), resultado
						.getString(3), resultado.getString(4), resultado
						.getString(5)));

			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return passwordList;
	}

	public ArrayList<CDataTableMaxCode> showMaxCode(String sQuery) {

		ArrayList<CDataTableMaxCode> aMaxCode;
		aMaxCode = new ArrayList<CDataTableMaxCode>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet queryAnswer = null;
		String consulta = sQuery;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			queryAnswer = declaracionConsulta.executeQuery(sQuery);

			while (queryAnswer.next()) 
			{
				aMaxCode.add(new CDataTableMaxCode(queryAnswer.getInt(1)));
			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return aMaxCode;
	}
	
	
	public ArrayList<CDataTableSale> showSale(String sQuery) {

		ArrayList<CDataTableSale> sale;
		sale = new ArrayList<CDataTableSale>();
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		ResultSet queryAnswer = null;
		String consulta = sQuery;

		try {

			Statement declaracionConsulta = conexion1.createStatement();
			queryAnswer = declaracionConsulta.executeQuery(sQuery);

			while (queryAnswer.next()) 
			{
				sale.add(new CDataTableSale(queryAnswer.getInt(1),queryAnswer.getString(2)
						,queryAnswer.getInt(3),queryAnswer.getInt(4)));
			}

		} catch (SQLException ex) {

			JOptionPane.showMessageDialog(null,
					"No se pudo efectuar la busqueda ", "Error",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}

		return sale;
	}
	
	

	public void deleteData(String consul) {
		conexionJDBC = new CLinkJDBC();
		conexion1 = (Connection) conexionJDBC.getConexion();

		try {
			PreparedStatement estamentoPreparado = (PreparedStatement) conexion1
					.prepareStatement(consul);
			estamentoPreparado.execute();

		} catch (com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException ex) {
			if (ex
					.toString()
					.contains(
							"com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`billar/empleados`, CONSTRAINT `empleados_ibfk_1` FOREIGN KEY (`ciudad`) REFERENCES `ciudad` (`codigo`) ON DELETE CASCADE ON UPDATE CASCADE)"))
				JOptionPane.showMessageDialog(null,
						"El dato seleccionado no esta en la base de datos ",
						"ERROR", JOptionPane.ERROR_MESSAGE);

			ex.printStackTrace();

		} catch (SQLException ex) {
			JOptionPane.showMessageDialog(null,
					"No se pudo ingresar a la base de datos", "ERROR",
					JOptionPane.ERROR_MESSAGE);
			ex.printStackTrace();
		}
	}

}
